
USE [school]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_Get_List

AS


				
				SELECT
					[RoleId]
				FROM
					[dbo].[Role]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Role table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_GetPaged
(

	@WhereClause varchar (8000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [RoleId] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([RoleId])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [RoleId]'
				SET @SQL = @SQL + ' FROM [dbo].[Role]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[RoleId]
				FROM
				    [dbo].[Role] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[RoleId] = PageIndex.[RoleId]
				ORDER BY
				    PageIndex.IndexId
                
				-- get row count
				SET @SQL = 'SELECT COUNT(1) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Role]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_Insert
(

	@RoleId int   
)
AS


				
				INSERT INTO [dbo].[Role]
					(
					[RoleId]
					)
				VALUES
					(
					@RoleId
					)
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_Update
(

	@RoleId int   ,

	@OriginalRoleId int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Role]
				SET
					[RoleId] = @RoleId
				WHERE
[RoleId] = @OriginalRoleId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_Delete
(

	@RoleId int   
)
AS


				DELETE FROM [dbo].[Role] WITH (ROWLOCK) 
				WHERE
					[RoleId] = @RoleId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_GetByRoleId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_GetByRoleId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_GetByRoleId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Role table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_GetByRoleId
(

	@RoleId int   
)
AS


				SELECT
					[RoleId]
				FROM
					[dbo].[Role]
				WHERE
					[RoleId] = @RoleId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Role_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Role_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Role_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Role table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Role_Find
(

	@SearchUsingOR bit   = null ,

	@RoleId int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [RoleId]
    FROM
	[dbo].[Role]
    WHERE 
	 ([RoleId] = @RoleId OR @RoleId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [RoleId]
    FROM
	[dbo].[Role]
    WHERE 
	 ([RoleId] = @RoleId AND @RoleId is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Group table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_Get_List

AS


				
				SELECT
					[GroupID],
					[GroupName]
				FROM
					[dbo].[Group]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Group table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_GetPaged
(

	@WhereClause varchar (8000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [GroupID] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([GroupID])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [GroupID]'
				SET @SQL = @SQL + ' FROM [dbo].[Group]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[GroupID], O.[GroupName]
				FROM
				    [dbo].[Group] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[GroupID] = PageIndex.[GroupID]
				ORDER BY
				    PageIndex.IndexId
                
				-- get row count
				SET @SQL = 'SELECT COUNT(1) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Group]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Group table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_Insert
(

	@GroupId int   ,

	@GroupName nvarchar (50)  
)
AS


				
				INSERT INTO [dbo].[Group]
					(
					[GroupID]
					,[GroupName]
					)
				VALUES
					(
					@GroupId
					,@GroupName
					)
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Group table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_Update
(

	@GroupId int   ,

	@OriginalGroupId int   ,

	@GroupName nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Group]
				SET
					[GroupID] = @GroupId
					,[GroupName] = @GroupName
				WHERE
[GroupID] = @OriginalGroupId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Group table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_Delete
(

	@GroupId int   
)
AS


				DELETE FROM [dbo].[Group] WITH (ROWLOCK) 
				WHERE
					[GroupID] = @GroupId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_GetByGroupId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_GetByGroupId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_GetByGroupId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Group table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_GetByGroupId
(

	@GroupId int   
)
AS


				SELECT
					[GroupID],
					[GroupName]
				FROM
					[dbo].[Group]
				WHERE
					[GroupID] = @GroupId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Group_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Group_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Group_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Group table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Group_Find
(

	@SearchUsingOR bit   = null ,

	@GroupId int   = null ,

	@GroupName nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [GroupID]
	, [GroupName]
    FROM
	[dbo].[Group]
    WHERE 
	 ([GroupID] = @GroupId OR @GroupId IS NULL)
	AND ([GroupName] = @GroupName OR @GroupName IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [GroupID]
	, [GroupName]
    FROM
	[dbo].[Group]
    WHERE 
	 ([GroupID] = @GroupId AND @GroupId is not null)
	OR ([GroupName] = @GroupName AND @GroupName is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Get_List

AS


				
				SELECT
					[Id],
					[UserName],
					[Email],
					[Address],
					[Birthdate],
					[Role],
					[Phone],
					[GroupId],
					[LastTimeLogin],
					[DateCreate],
					[Password]
				FROM
					[dbo].[Users]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Users table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_GetPaged
(

	@WhereClause varchar (8000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[Users]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[Id], O.[UserName], O.[Email], O.[Address], O.[Birthdate], O.[Role], O.[Phone], O.[GroupId], O.[LastTimeLogin], O.[DateCreate], O.[Password]
				FROM
				    [dbo].[Users] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
                
				-- get row count
				SET @SQL = 'SELECT COUNT(1) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Users]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Insert
(

	@Id int   ,

	@UserName nvarchar (50)  ,

	@Email nvarchar (150)  ,

	@Address nvarchar (200)  ,

	@Birthdate date   ,

	@Role int   ,

	@Phone nvarchar (12)  ,

	@GroupId int   ,

	@LastTimeLogin datetime   ,

	@DateCreate datetime   ,

	@Password binary (50)  
)
AS


				
				INSERT INTO [dbo].[Users]
					(
					[Id]
					,[UserName]
					,[Email]
					,[Address]
					,[Birthdate]
					,[Role]
					,[Phone]
					,[GroupId]
					,[LastTimeLogin]
					,[DateCreate]
					,[Password]
					)
				VALUES
					(
					@Id
					,@UserName
					,@Email
					,@Address
					,@Birthdate
					,@Role
					,@Phone
					,@GroupId
					,@LastTimeLogin
					,@DateCreate
					,@Password
					)
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Update
(

	@Id int   ,

	@OriginalId int   ,

	@UserName nvarchar (50)  ,

	@Email nvarchar (150)  ,

	@Address nvarchar (200)  ,

	@Birthdate date   ,

	@Role int   ,

	@Phone nvarchar (12)  ,

	@GroupId int   ,

	@LastTimeLogin datetime   ,

	@DateCreate datetime   ,

	@Password binary (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Users]
				SET
					[Id] = @Id
					,[UserName] = @UserName
					,[Email] = @Email
					,[Address] = @Address
					,[Birthdate] = @Birthdate
					,[Role] = @Role
					,[Phone] = @Phone
					,[GroupId] = @GroupId
					,[LastTimeLogin] = @LastTimeLogin
					,[DateCreate] = @DateCreate
					,[Password] = @Password
				WHERE
[Id] = @OriginalId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Users table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Users] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_GetByGroupId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_GetByGroupId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_GetByGroupId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Users table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_GetByGroupId
(

	@GroupId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[UserName],
					[Email],
					[Address],
					[Birthdate],
					[Role],
					[Phone],
					[GroupId],
					[LastTimeLogin],
					[DateCreate],
					[Password]
				FROM
					[dbo].[Users]
				WHERE
					[GroupId] = @GroupId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Users table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[UserName],
					[Email],
					[Address],
					[Birthdate],
					[Role],
					[Phone],
					[GroupId],
					[LastTimeLogin],
					[DateCreate],
					[Password]
				FROM
					[dbo].[Users]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Users_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Users_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Users_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Users table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Users_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@UserName nvarchar (50)  = null ,

	@Email nvarchar (150)  = null ,

	@Address nvarchar (200)  = null ,

	@Birthdate date   = null ,

	@Role int   = null ,

	@Phone nvarchar (12)  = null ,

	@GroupId int   = null ,

	@LastTimeLogin datetime   = null ,

	@DateCreate datetime   = null ,

	@Password binary (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [UserName]
	, [Email]
	, [Address]
	, [Birthdate]
	, [Role]
	, [Phone]
	, [GroupId]
	, [LastTimeLogin]
	, [DateCreate]
	, [Password]
    FROM
	[dbo].[Users]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([UserName] = @UserName OR @UserName IS NULL)
	AND ([Email] = @Email OR @Email IS NULL)
	AND ([Address] = @Address OR @Address IS NULL)
	AND ([Birthdate] = @Birthdate OR @Birthdate IS NULL)
	AND ([Role] = @Role OR @Role IS NULL)
	AND ([Phone] = @Phone OR @Phone IS NULL)
	AND ([GroupId] = @GroupId OR @GroupId IS NULL)
	AND ([LastTimeLogin] = @LastTimeLogin OR @LastTimeLogin IS NULL)
	AND ([DateCreate] = @DateCreate OR @DateCreate IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [UserName]
	, [Email]
	, [Address]
	, [Birthdate]
	, [Role]
	, [Phone]
	, [GroupId]
	, [LastTimeLogin]
	, [DateCreate]
	, [Password]
    FROM
	[dbo].[Users]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([UserName] = @UserName AND @UserName is not null)
	OR ([Email] = @Email AND @Email is not null)
	OR ([Address] = @Address AND @Address is not null)
	OR ([Birthdate] = @Birthdate AND @Birthdate is not null)
	OR ([Role] = @Role AND @Role is not null)
	OR ([Phone] = @Phone AND @Phone is not null)
	OR ([GroupId] = @GroupId AND @GroupId is not null)
	OR ([LastTimeLogin] = @LastTimeLogin AND @LastTimeLogin is not null)
	OR ([DateCreate] = @DateCreate AND @DateCreate is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the Paramerter table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_Get_List

AS


				
				SELECT
					[Id],
					[ParamerterName],
					[ParamerterValue],
					[DataType],
					[Description],
					[UserEditable]
				FROM
					[dbo].[Paramerter]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the Paramerter table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_GetPaged
(

	@WhereClause varchar (8000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int, [ParamerterName] nvarchar(50)  
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id], [ParamerterName])'
				SET @SQL = @SQL + ' SELECT'
				SET @SQL = @SQL + ' [Id], [ParamerterName]'
				SET @SQL = @SQL + ' FROM [dbo].[Paramerter]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Only get the number of rows needed here.
				SET ROWCOUNT @PageUpperBound
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Reset Rowcount back to all
				SET ROWCOUNT 0
				
				-- Return paged results
				SELECT O.[Id], O.[ParamerterName], O.[ParamerterValue], O.[DataType], O.[Description], O.[UserEditable]
				FROM
				    [dbo].[Paramerter] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
					AND O.[ParamerterName] = PageIndex.[ParamerterName]
				ORDER BY
				    PageIndex.IndexId
                
				-- get row count
				SET @SQL = 'SELECT COUNT(1) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Paramerter]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the Paramerter table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_Insert
(

	@Id int    OUTPUT,

	@ParamerterName nvarchar (50)  ,

	@ParamerterValue nvarchar (50)  ,

	@DataType nvarchar (50)  ,

	@Description nvarchar (500)  ,

	@UserEditable bit   
)
AS


				
				INSERT INTO [dbo].[Paramerter]
					(
					[ParamerterName]
					,[ParamerterValue]
					,[DataType]
					,[Description]
					,[UserEditable]
					)
				VALUES
					(
					@ParamerterName
					,@ParamerterValue
					,@DataType
					,@Description
					,@UserEditable
					)
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the Paramerter table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_Update
(

	@Id int   ,

	@ParamerterName nvarchar (50)  ,

	@OriginalParamerterName nvarchar (50)  ,

	@ParamerterValue nvarchar (50)  ,

	@DataType nvarchar (50)  ,

	@Description nvarchar (500)  ,

	@UserEditable bit   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Paramerter]
				SET
					[ParamerterName] = @ParamerterName
					,[ParamerterValue] = @ParamerterValue
					,[DataType] = @DataType
					,[Description] = @Description
					,[UserEditable] = @UserEditable
				WHERE
[Id] = @Id 
AND [ParamerterName] = @OriginalParamerterName 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the Paramerter table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_Delete
(

	@Id int   ,

	@ParamerterName nvarchar (50)  
)
AS


				DELETE FROM [dbo].[Paramerter] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					AND [ParamerterName] = @ParamerterName
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_GetByIdParamerterName procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_GetByIdParamerterName') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_GetByIdParamerterName
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the Paramerter table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_GetByIdParamerterName
(

	@Id int   ,

	@ParamerterName nvarchar (50)  
)
AS


				SELECT
					[Id],
					[ParamerterName],
					[ParamerterValue],
					[DataType],
					[Description],
					[UserEditable]
				FROM
					[dbo].[Paramerter]
				WHERE
					[Id] = @Id
					AND [ParamerterName] = @ParamerterName
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Paramerter_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Paramerter_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Paramerter_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the Paramerter table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Paramerter_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ParamerterName nvarchar (50)  = null ,

	@ParamerterValue nvarchar (50)  = null ,

	@DataType nvarchar (50)  = null ,

	@Description nvarchar (500)  = null ,

	@UserEditable bit   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [ParamerterName]
	, [ParamerterValue]
	, [DataType]
	, [Description]
	, [UserEditable]
    FROM
	[dbo].[Paramerter]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([ParamerterName] = @ParamerterName OR @ParamerterName IS NULL)
	AND ([ParamerterValue] = @ParamerterValue OR @ParamerterValue IS NULL)
	AND ([DataType] = @DataType OR @DataType IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
	AND ([UserEditable] = @UserEditable OR @UserEditable IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [ParamerterName]
	, [ParamerterValue]
	, [DataType]
	, [Description]
	, [UserEditable]
    FROM
	[dbo].[Paramerter]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([ParamerterName] = @ParamerterName AND @ParamerterName is not null)
	OR ([ParamerterValue] = @ParamerterValue AND @ParamerterValue is not null)
	OR ([DataType] = @DataType AND @DataType is not null)
	OR ([Description] = @Description AND @Description is not null)
	OR ([UserEditable] = @UserEditable AND @UserEditable is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

